Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine
Imports System.Data
Imports System.Data.SqlClient
Imports Sicop.Classi
Imports System.IO
Imports System.Configuration
Imports System.Web.HttpContext
Imports System.Web.UI.WebControls
Imports GemBox.ExcelLite


Public Class cCreaReport

    Dim settore As String
    Dim appPath As String
    Dim ds As xmlIscritto
    Dim dsPrat As xmlPraticante
    Dim err As cException

    Sub caricoReport(ByVal ds As DataSet, ByVal path As String, ByVal outputFile As String, ByVal titolo As String)
        Dim orpt As New ReportDocument

        orpt.Load(path)

        orpt.SummaryInfo.ReportTitle = titolo
        Dim crv1 As New CrystalDecisions.Web.CrystalReportViewer

        crv1.ReportSource = orpt

        ' This is the Crystal Report file created at Design Time
        orpt.SetDataSource(ds)

        'If nome = "PratScheda" Then
        '    orpt.ReportDefinition.ReportObjects.Item("Subreport5").Top = orpt.ReportDefinition.ReportObjects.Item("Subreport2").Top + 400 + ((orpt.ReportDefinition.ReportObjects.Item("Subreport2").Height + 200) * ds.Tables("Professionisti").Rows.Count)
        'End If

        Dim oStream As System.IO.MemoryStream
        'esporto in pdf
        'oStream = orpt.ExportToStream(ExportFormatType.PortableDocFormat)

        'esporto in word
        oStream = orpt.ExportToStream(ExportFormatType.WordForWindows)
        ' salvo il report in un file
        Dim f As FileStream = File.OpenWrite(ConfigurationSettings.AppSettings.Get("sPath") & outputFile)
        f.Write(oStream.ToArray(), 0, oStream.Length)
        f.Close()

        orpt.Close()
        orpt.Dispose()
        oStream.Close()


    End Sub

    Sub caricoReport(ByVal ds As DataSet, ByVal path As String, ByVal outputFile As String)
        Dim orpt As New ReportDocument
        ' Dim reportPath As String = appPath & ConfigurationSettings.AppSettings.Get("pathReport")

        orpt.Load(path)


        Dim crv1 As New CrystalDecisions.Web.CrystalReportViewer

        crv1.ReportSource = orpt

        ' This is the Crystal Report file created at Design Time
        orpt.SetDataSource(ds)

        'If nome = "PratScheda" Then
        '    orpt.ReportDefinition.ReportObjects.Item("Subreport5").Top = orpt.ReportDefinition.ReportObjects.Item("Subreport2").Top + 400 + ((orpt.ReportDefinition.ReportObjects.Item("Subreport2").Height + 200) * ds.Tables("Professionisti").Rows.Count)
        'End If

        Dim oStream As System.IO.MemoryStream
        'esporto in pdf
        'oStream = orpt.ExportToStream(ExportFormatType.PortableDocFormat)

        'esporto in word
        oStream = orpt.ExportToStream(ExportFormatType.WordForWindows)
        ' salvo il report in un file
        Dim f As FileStream = File.OpenWrite(ConfigurationSettings.AppSettings.Get("sPath") & outputFile)
        f.Write(oStream.ToArray(), 0, oStream.Length)
        f.Close()

        orpt.Close()
        orpt.Dispose()
        oStream.Close()


    End Sub

    Sub caricoReport(ByVal dt As DataTable, ByVal path As String, ByVal outputFile As String)
        Dim orpt As New ReportDocument
        ' Dim reportPath As String = appPath & ConfigurationSettings.AppSettings.Get("pathReport")
        orpt.Load(path)


        Dim crv1 As New CrystalDecisions.Web.CrystalReportViewer

        crv1.ReportSource = orpt

        ' This is the Crystal Report file created at Design Time
        orpt.SetDataSource(dt)

        Dim oStream As System.IO.MemoryStream
        'esporto in pdf
        'oStream = orpt.ExportToStream(ExportFormatType.PortableDocFormat)

        'esporto in word
        oStream = orpt.ExportToStream(ExportFormatType.WordForWindows)
        ' salvo il report in un file
        Dim f As FileStream = File.OpenWrite(ConfigurationSettings.AppSettings.Get("sPath") & outputFile)
        f.Write(oStream.ToArray(), 0, oStream.Length)
        f.Close()

        orpt.Close()
        orpt.Dispose()
        oStream.Close()


    End Sub

    Sub CreaEtichette(ByVal tipoInd As String, ByVal idLingua As String, ByVal nomeReport As String, ByVal pos As Object, ByVal path As String)
        Dim myDAdp As SqlDataAdapter = New SqlDataAdapter
        Dim sql As String
        Dim ds As xmlEtichette = New xmlEtichette
        Dim conn As New Connessione

        Dim objStat As Stat = New Statistiche(False).Statistica
        Dim sId As String
        Dim sIdAna As String
        Dim sIdProf As String
        Dim dt As DataTable = objStat.ris
        Dim dr As DataRow

        Dim destDir As System.IO.DirectoryInfo = New DirectoryInfo(ConfigurationSettings.AppSettings.Get("sPath"))

        If Not (destDir.Exists) Then
            destDir.Create()
        End If

        If pos Is System.DBNull.Value Then
            pos = 1
        ElseIf pos = "" Or Not IsNumeric(pos) Then
            pos = 1
        End If

        For Each dr In dt.Rows
            If dr("idAna") <> "0" Then
                sIdAna += dr("idAna") & ", "
            End If
            If dr("idProf") <> "0" Then
                sIdProf += dr("idProf") & ", "
            End If
            If dr("id") <> "0" Then
                sId += dr("id") & ", "
            End If
        Next

        If sId <> "" Then
            sId = "(" & Left(sId, Len(sId) - 2) & ")"
        End If
        If sIdAna <> "" Then
            sIdAna = "(" & Left(sIdAna, Len(sIdAna) - 2) & ")"
        End If
        If sIdProf <> "" Then
            sIdProf = "(" & Left(sIdProf, Len(sIdProf) - 2) & ")"
        End If


        If sIdAna <> "" Then
            sql = "SELECT distinct intest, Cognome  + ' ' + Nome as nome, Indirizzo = case when Indirizzo is null then '' else Indirizzo end,Indirizzo2 = case when Indirizzo2 is null then '' else Indirizzo2 end,NumCivico = case when NumCivico is null then '' else NumCivico end, cap = case when cap is null then '' else cap end, Citta=case when citta ='' then  desc_comune else citta end, provincia = case when provincia is null then '' else provincia end fROM  Anagrafica left JOIN " & _
                      "Indirizzi ON Anagrafica.ID = Indirizzi.Id_ana  where TipoIndirizzo = " & tipoInd & " and p_contatti=0 and anagrafica.id in " & sIdAna & " and idLingua= " & idLingua
        End If
        If sId <> "" Then
            If sql <> "" Then sql += " union "
            sql += " SELECT distinct intest, nominativo as nome, Indirizzo = case when Indirizzo is null then '' else Indirizzo end, cap, Citta, Provincia= case when provincia is null then '' else provincia end fROM  rubrica " & _
                      " where id in " & sId
        End If
        If sIdProf <> "" Then
            If sql <> "" Then sql += " union "
            sql += " SELECT distinct intest, nome=case when nominativo<>'' then nominativo else cognome + ' ' + nome end , Indirizzo, cap, Citta, provincia fROM  professionisti " & _
                      " where id in " & sIdProf
        End If

        If System.Web.HttpContext.Current.Session("albo") = "13" Then
            sql += " order by cap"
        Else
            sql += " order by nome"
        End If

        myDAdp.SelectCommand = New SqlCommand(sql, conn.getConnection)
        myDAdp.SelectCommand.CommandTimeout = 72000
        myDAdp.Fill(ds, "dsAnagrafica")


        'se devo partire da una pos>1, aggiungo delle righe vuote
        If pos > 1 Then
            Dim x, y As Int16
            For x = 1 To pos
                dr = ds.Tables("dsAnagrafica").NewRow
                For y = 0 To dr.ItemArray.GetUpperBound(0)
                    dr.Item(y) = ""
                Next
                ds.Tables("dsAnagrafica").Rows.InsertAt(dr, 0)
            Next
        End If

        Dim orpt As New ReportDocument
        orpt.Load(path & ConfigurationSettings.AppSettings.Get("pathReport") & nomeReport & ".rpt")

        Dim crv1 As New CrystalDecisions.Web.CrystalReportViewer
        crv1.ReportSource = orpt

        ' This is the Crystal Report file created at Design Time
        orpt.SetDataSource(ds)

        Dim oStream As System.IO.MemoryStream
        'esporto in pdf
        'oStream = orpt.ExportToStream(ExportFormatType.PortableDocFormat)

        'esporto in word
        oStream = orpt.ExportToStream(ExportFormatType.WordForWindows)
        ' salvo il report in un file
        Dim f As FileStream = File.OpenWrite(ConfigurationSettings.AppSettings.Get("sPath") & "etichette.doc")
        f.Write(oStream.ToArray(), 0, oStream.Length)
        f.Close()

        orpt.Close()
        orpt.Dispose()
        oStream.Close()

    End Sub

    Sub EtichetteInExcell(ByVal tipoInd As String, ByVal idLingua As String, ByVal path As String)
        Dim myDAdp As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim sql As String
        Dim ds As xmlEtichette = New xmlEtichette
        Dim conn As New Connessione

        Dim objStat As Stat = New Statistiche(False).Statistica
        Dim sId As String
        Dim sIdAna As String
        Dim sIdProf As String
        Dim dt As DataTable = objStat.ris
        Dim dr As DataRow

        Dim destDir As System.IO.DirectoryInfo = New System.IO.DirectoryInfo(ConfigurationSettings.AppSettings.Get("sPath"))

        If Not (destDir.Exists) Then
            destDir.Create()
        End If

        For Each dr In dt.Rows
            If dr("idAna") <> "0" Then
                sIdAna += dr("idAna") & ", "
            End If
            If dr("idProf") <> "0" Then
                sIdProf += dr("idProf") & ", "
            End If
            If dr("id") <> "0" Then
                sId += dr("id") & ", "
            End If
        Next

        If sId <> "" Then
            sId = "(" & Left(sId, Len(sId) - 2) & ")"
        End If
        If sIdAna <> "" Then
            sIdAna = "(" & Left(sIdAna, Len(sIdAna) - 2) & ")"
        End If
        If sIdProf <> "" Then
            sIdProf = "(" & Left(sIdProf, Len(sIdProf) - 2) & ")"
        End If


        If sIdAna <> "" Then
            sql = "SELECT distinct intest, Cognome  + ' ' + Nome as nome, Indirizzo = case when Indirizzo is null then '' else Indirizzo end,Indirizzo2 = case when Indirizzo2 is null then '' else Indirizzo2 end,NumCivico = case when NumCivico is null then '' else NumCivico end, cap = case when cap is null then '' else cap end, Citta=case when citta ='' then  desc_comune else citta end, provincia = case when provincia is null then '' else provincia end fROM  Anagrafica left JOIN " & _
                      "Indirizzi ON Anagrafica.ID = Indirizzi.Id_ana  where TipoIndirizzo = " & tipoInd & " and p_contatti=0 and anagrafica.id in " & sIdAna & " and idLingua= " & idLingua
        End If
        If sId <> "" Then
            If sql <> "" Then sql += " union "
            sql += " SELECT distinct intest, nominativo as nome, Indirizzo = case when Indirizzo is null then '' else Indirizzo end, cap, Citta, Provincia= case when provincia is null then '' else provincia end fROM  rubrica " & _
                      " where id in " & sId
        End If
        If sIdProf <> "" Then
            If sql <> "" Then sql += " union "
            sql += " SELECT distinct intest, nome=case when nominativo<>'' then nominativo else cognome + ' ' + nome end , Indirizzo, cap, Citta, provincia fROM  professionisti " & _
                      " where id in " & sIdProf
        End If

        If System.Web.HttpContext.Current.Session("albo") = "13" Then
            sql += " order by cap"
        Else
            sql += " order by nome"
        End If

        myDAdp.SelectCommand = New SqlCommand(sql, conn.getConnection)
        myDAdp.SelectCommand.CommandTimeout = 72000
        myDAdp.Fill(ds, "dsAnagrafica")

        ' Esporto
        Dim i As Integer = 0
        Dim r As Integer = 0
        ExcelLite.SetLicense("EEFP-5N69-HTTU-DMGS")
        Dim excelFile As excelFile = New excelFile
        Dim worksheets As ExcelWorksheetCollection = excelFile.Worksheets
        Dim ws As ExcelWorksheet
        i = ds.dsAnagrafica.Count
        ws = worksheets.Add("Etichette")
        ws.InsertDataTable(ds.dsAnagrafica, 0, 0, True)

        ws.Rows(0).Style.Font.Weight = ExcelFont.BoldWeight

        For r = 0 To i
            ws.Columns(r).AutoFit()
        Next


        worksheets.ActiveWorksheet = worksheets("Etichette")

        Dim fileName As String = ConfigurationSettings.AppSettings.Get("sPath") & "Etichette.xls"

        excelFile.SaveXls(fileName)


    End Sub


    Public Shared Sub DataGridToExcel()
        Dim str As String = ""
        Dim colonne As String = ""
        Dim header As String = ""
        Dim righe As String = ""
        Dim DR As DataRow
        Dim DC As DataColumn
        Dim i As Integer = 0
        Dim r As Integer = 0
        Dim DT As DataTable = System.Web.HttpContext.Current.Session("risultati")

        i = DT.Columns.Count

        'GemBox

        ExcelLite.SetLicense("EEFP-5N69-HTTU-DMGS")

        Dim excelFile As excelFile = New excelFile
        Dim worksheets As ExcelWorksheetCollection = excelFile.Worksheets
        Dim ws As ExcelWorksheet

        ws = worksheets.Add("Values")
        ws.InsertDataTable(DT, 0, 0, True)

        ws.Rows(0).Style.Font.Weight = ExcelFont.BoldWeight

        For r = 0 To i
            ws.Columns(r).AutoFit()
        Next


        worksheets.ActiveWorksheet = worksheets("Values")

        Dim fileName As String = ConfigurationSettings.AppSettings.Get("sPath") & "query.xls"

        excelFile.SaveXls(fileName)


    End Sub

    Function CreateData() As DataTable
        Dim dt As DataTable = New DataTable

        dt.Columns.Add("ID", Type.GetType("System.Int32"))
        dt.Columns.Add("FirstName", Type.GetType("System.String"))
        dt.Columns.Add("LastName", Type.GetType("System.String"))

        dt.Rows.Add(New Object() {100, "John", "Doe"})
        dt.Rows.Add(New Object() {101, "Fred", "Nurk"})
        dt.Rows.Add(New Object() {103, "Hans", "Meier"})
        dt.Rows.Add(New Object() {104, "Ivan", "Horvat"})
        dt.Rows.Add(New Object() {105, "Jean", "Dupont"})
        dt.Rows.Add(New Object() {106, "Mario", "Rossi"})

        Return dt
    End Function

    Function xmlParser(ByVal nome As String)
        Dim xmlDoc As New System.Xml.XmlDocument
        Dim xmlNode As System.Xml.XmlNode
        Dim cont As Int16 = 1
        Dim conn As New Connessione
        Dim sTesto As String
        Dim s, tabella, campo As String
        Dim pos, pos1, pos2 As Int16
        Dim sXml As String = conn.getValore("select xmlTesto from certificati where nome_file='" & nome & "' and albo=" & ConfigurationSettings.AppSettings.Get("Albo"))

        If sXml <> "" Then
            xmlDoc.LoadXml(sXml)
            conn.eseguiSql("insert into tempTesti (sessionId, testo1, testo2, testo3, testo4, testo5, testo6, testo7, testo8, testo9, testo10) values('" & System.Web.HttpContext.Current.Session.SessionID & "', '', '', '', '', '', '', '', '', '', '')")

            For Each xmlNode In xmlDoc.DocumentElement.ChildNodes
                sTesto = xmlNode.InnerText
                pos = InStr(sTesto, "$")
                Do While pos > 0
                    pos1 = InStr(pos + 1, sTesto, "$")
                    s = Mid(sTesto, pos + 1, pos1 - pos - 1)
                    pos2 = InStr(s, ".")
                    tabella = Left(s, pos2 - 1)
                    campo = Mid(s, pos2 + 1)
                    Try
                        sTesto = Replace(sTesto, "$" & s & "$", ds.Tables(tabella).Rows(0)(campo))
                    Catch ex As Exception
                        sTesto = Replace(sTesto, "$" & s & "$", "")
                    End Try

                    pos = InStr(sTesto, "$")
                Loop

                conn.eseguiSql("update tempTesti set testo" & cont & "='" & Replace(sTesto, "'", "''") & "' where sessionId='" & System.Web.HttpContext.Current.Session.SessionID & "'")
                cont += 1
            Next


            Dim myDAdp As SqlDataAdapter = New SqlDataAdapter
            Dim sql As String = "select testo1, testo2, testo3, testo4, testo5, testo6, testo7, testo8, testo9, testo10 from tempTesti where sessionId='" & System.Web.HttpContext.Current.Session.SessionID & "'"
            myDAdp.SelectCommand = New SqlCommand(sql, conn.getConnection)
            myDAdp.SelectCommand.CommandTimeout = 72000
            myDAdp.Fill(ds, "testo")

            conn.eseguiSql("delete from tempTesti where sessionId='" & System.Web.HttpContext.Current.Session.SessionID & "'")
        End If

    End Function


End Class
